package com.kaigejava.main;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.kaigejava.pojo.SignInfo;
import org.apache.commons.lang3.time.DateUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @description
 * @since 2022/5/7 16:10
 */
public class Test {

    @org.junit.Test
    public  void subTest(){
        List<Integer> list = new ArrayList<>();
        list.add(0);
        list.add(1);
        list.add(2);
        list.add(3);
        list.add(4);
        list.add(5);

        System.out.println(list.subList(0,2));
        System.out.println(list.subList(1,3));


    }


    @org.junit.Test
    public void updateusersystemLetterTest(){
        /**
         * ALTER TABLE `hwyc_message_`.`b_user_system_letter_1002`
         * ADD COLUMN `letter_type` tinyint(4) NULL COMMENT '站内信类型: 1-系统消息 2-书籍 3-活动 ' AFTER `status`;
         */
        /**
         * ALTER TABLE `hwyc_message_0`.`b_user_system_letter_217`
         * DROP COLUMN `latter_type`;
         */
        StringBuilder sql1 = new StringBuilder();
        String str2 = " ADD COLUMN `platform` tinyint(4) NULL DEFAULT 0 COMMENT '平台 0：所有 2：ios 3:android'  AFTER `status`;";
        for(int x = 768;x<1024;x++){
            int dbNo = 0;
            if (x <= 255) {
                dbNo = 0;
            } else if (x <= 511) {
                dbNo = 1;
            } else if (x <= 767) {
                dbNo = 2;
            } else if (x <= 1023) {
                dbNo = 3;
            }
            String s1 = "ALTER TABLE `hwyc_message_"+dbNo+"`.`b_user_system_letter_"+x+"`";
            sql1.append(s1);
            sql1.append(str2);
            sql1.append(" \r\n ");
        }
        System.out.println(sql1);
    }


    @org.junit.Test
    public void updateSqlTest(){
        /**
         * ALTER TABLE `hwyc_user_0`.`user_0`
         * MODIFY COLUMN `member_type` tinyint(4) NOT NULL DEFAULT 1 COMMENT '会员类型,1-订阅会员；2-金币包会员；3-限时次卡会员；' AFTER `member_subscription_status`,
         * ADD COLUMN `times_limited_expire_time` datetime NULL COMMENT '限时次卡会员到期时间' AFTER `coins_member_expire_time`;
         */
        StringBuilder sql1 = new StringBuilder();
        String str = " MODIFY COLUMN `member_type` tinyint(4) NOT NULL DEFAULT 1 COMMENT '会员类型,1-订阅会员；2-金币包会员；3-限时次卡会员；' AFTER `member_subscription_status`,";
        String str2 = " ADD COLUMN `times_limited_expire_time` datetime NULL COMMENT '限时次卡会员到期时间' AFTER `coins_member_expire_time`;";
        for(int x = 0;x<1024;x++){
            int dbNo = 0;
            if (x <= 255) {
                dbNo = 0;
            } else if (x <= 511) {
                dbNo = 1;
            } else if (x <= 767) {
                dbNo = 2;
            } else if (x <= 1023) {
                dbNo = 3;
            }
            String s1 = "ALTER TABLE `hwyc_user_"+dbNo+"`.`user_"+x+"`";
            sql1.append(s1);
            sql1.append(str);
            sql1.append(str2);
            sql1.append(" \r\n ");
        }
        System.out.println(sql1);
    }

    @org.junit.Test
    public void test() {
        String fname = "6";
        List<SignInfo> list = readList(fname);
        System.out.println("获取到总数为：" + list.size());
        StringBuilder sql1 = new StringBuilder();
        StringBuilder sql2 = new StringBuilder();

        StringBuilder sql191 = new StringBuilder();
        StringBuilder sql192 = new StringBuilder();

        //SELECT user_id, count(*),pline from  hwyc_consume_0.b_recharge_h_240 where user_id = 22351088 and type =4 and app_platform!='h5'  and ctime >= '2022-05-03 00:00:00' and ctime <= '2022-05-07 00:00:00' union all
        for (SignInfo info : list) {
            Long userId = info.getUserId();
            Long x = userId % 1024;
            int dbNo = 0;
            if (x <= 255) {
                dbNo = 0;
            } else if (x <= 511) {
                dbNo = 1;
            } else if (x <= 767) {
                dbNo = 2;
            } else if (x <= 1023) {
                dbNo = 3;
            }
            Integer[] arr = new Integer[]{4, 19};
            for (Integer type : arr) {
                boolean b = true;
                String s = "SELECT user_id, count(*),pline from  hwyc_consume_" + dbNo + ".b_recharge_h_" + x
                        + " where  user_id = " + info.getUserId() + " and app_platform!='h5'  ";
                if (info.getPline() == 1) {
                    Date querStartTime = DateUtils.addDays(info.getSubscriptionTime(), 1);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    String d = "2022-05-0" + fname;
                    Date day = null;
                    try {
                        day = sdf.parse(d);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    if (querStartTime.before(day)) {
                        s += " and ctime >= '2022-05-0" + fname + " 00:00:00' and ctime <= '2022-05-0" + fname + " 23:59:59'  ";
                    } else {
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        String stimeStr = sdf.format(querStartTime);
                        // System.out.println("userId:"+info.getUserId()+"stimeStr:"+stimeStr);
                        b = false;
                    }
                } else {
                    s += " and ctime >= '2022-05-0" + fname + " 00:00:00' and ctime <= '2022-05-0" + fname + " 23:59:59'  ";
                }
                //union all
                if (type == 4) {
                    s += " and type = " + type;
                    s += " union all  \r\n ";
                    if (b) {
                        if (dbNo < 2) {
                            sql1.append(s);
                        } else {
                            sql2.append(s);
                        }
                    }

                } else {
                    s += " and type = " + type;
                    s += " union all  \r\n ";
                    if (b) {
                        if (dbNo < 2) {
                            sql191.append(s);
                        } else {
                            sql192.append(s);
                        }
                    }
                }
            }

        }
        System.out.println(sql1);
        System.out.println("======222========");
        System.out.println(sql191);


        System.out.println("===================");
        System.out.println(sql2);
        System.out.println("======333========");
        System.out.println(sql192);


    }

    public List<SignInfo> readList(String fname) {
        List<SignInfo> list = new ArrayList<>();
        // 读取的excel文件路径
        String filename = "C:\\Users\\n\\Desktop\\签到2\\" + fname + "号.xlsx";
        // 读取excel
        EasyExcel.read(filename, SignInfo.class, new AnalysisEventListener<SignInfo>() {
            // 每解析一行数据,该方法会被调用一次
            @Override
            public void invoke(SignInfo signInfo, AnalysisContext analysisContext) {
                list.add(signInfo);
            }

            // 全部解析完成被调用
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("解析完成...");
                // 可以将解析的数据保存到数据库
            }
        }).sheet().doRead();

        return list;
    }
}
